import pymysql
import configparser

config = configparser.ConfigParser()
config.read('config.cfg', encoding='utf-8')


def insert_goods(name, price, des, img, uploader, gid):
    """
    add an item to goods table
    :param gid: when upload, origin gid, otherwise '-1'
    :param name: string, item name
    :param price: string, item price like '10.50'
    :param des: string, description
    :param img: string, path of item picture, must stored in ../static/img/
    :param uploader: string, name of the uploader
    :return: null
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('select uID from user where uName="%s"' % uploader)
    uID = str(cursor.fetchone()[0])

    cursor.execute('call add_upload_goods("%s","%s",%s,"%s",%s,%s)' % (name, des, price, img, uID, gid))
    db.commit()

    cursor.close()
    db.close()


def get_some_goods(num):
    """
    randomly select some items from goods table
    :param num: number of items selected
    :return: dictionary list of selected
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('call get_recommend(%d)' % num)
    result = cursor.fetchall()
    info = []
    for item in result:
        info.append({
            'gid': item[0],
            'name': item[1],
            'des': item[2],
            'price': str(item[3]),
            'img': item[4]
        })
    cursor.close()
    db.close()
    return info


def login_check(id, psw):
    """
    query the db and check if id matches password
    :param id: user id
    :param psw: password
    :return: if matches, return 0, else return 1
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('select * from user where uName="%s"' % id)
    result = cursor.fetchone()
    print(result)
    uid = -1
    authority = -1
    if result is None:
        status = 1
    elif result[2] == psw:
        status = 0
        uid = result[0]
        authority = result[9]
    else:
        status = 1

    cursor.close()
    db.close()
    return status, uid, authority


def insert_user(name, psw, cps, sid, tel, vx, qq, email):
    """
    add a new user in user table, default authority is 1(lowest)
    :param vx: user's wechat id
    :param qq: user's qq
    :param email: user's email
    :param name: user's name
    :param psw: user's password
    :param cps: user in which campus, ChangAn or YouYi
    :param sid: user's school id
    :param tel: user's phone number
    :return: null
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()
    result = 'good'
    try:
        cursor.execute('insert into user(uName,uPsw,uCampus,schoolID,uTel,uVX,uQQ,uEmail) values("%s","%s","%s","%s",'
                       '"%s","%s","%s","%s")' % (name, psw, cps, sid, tel, vx, qq, email))
        db.commit()
    except Exception as e:
        print(e)
        print('create user failed.')
        result = 'fail'
    finally:
        cursor.close()
        db.close()
        return result


def get_item_info(gid):
    """
    get full info of a goods item, including published info and comments
    :param gid: gID od the item
    :return: two dictionary, main info and comment
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('select goods.*,user.uName from goods,user where gID=%d and goods.uploader=user.uID' % gid)
    result_info = cursor.fetchone()
    goods_info = {
        'gid': str(gid),
        'name': result_info[1],
        'des': result_info[2],
        'price': str(result_info[3]),
        'img_src': result_info[4][3:],
        'seller_id': result_info[5],
        'seller_name': result_info[8],
        'time': result_info[6].strftime('%Y-%m-%d')
    }

    cursor.execute('call get_goods_comment(%d)' % gid)
    result_comment = cursor.fetchall()
    comment_info = []
    for c in result_comment:
        comment_info.append({
            'rid': None,
            'cid': c[0],
            'commenter': c[1],
            'replyto': c[2],
            'time': str(c[3]),
            'text': c[4]
        })
    # cursor.execute('call get_goods_reply(%d)' % gid)
    # result_reply = cursor.fetchall()
    # for r in result_reply:
    #     comment_info.append({
    #         'rid': r[0],
    #         'cid': None,
    #         'commenter': r[1],
    #         'replyto': r[2],
    #         'time': str(r[3]),
    #         'text': r[4]
    #     })
    cursor.close()
    db.close()
    return goods_info, comment_info


def insert_comment(gid, time, commenter, text, reply_to):
    """
    add a new comment to table comment
    :param gid: comment to which item, require gID
    :param time: comment time in format 'YYYY-MM-DD HH:MM:SS'
    :param commenter: who made this comment, require uName
    :param text: content of the comment
    :param reply_to: uID of whom the comment reply to
    :return: null
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('select uID from user where uName="%s"' % commenter)
    uID = str(cursor.fetchone()[0])

    if reply_to == '':
        reply_to = 'null'
    cursor.execute('call add_comment(%s,%s,"%s","%s")' % (uID, gid, reply_to, text))
    db.commit()

    cursor.close()
    db.close()


def remove_comment(cid):
    """
    remove a comment
    :param cid: cid
    :return: null
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('delete from comments where cid=%s' % cid)
    db.commit()

    cursor.close()
    db.close()


def new_deal(buyer_id, seller_id, gid):
    """
    create a new deal, price will be set as uploaded price
    :param buyer_id: who buys the item
    :param seller_id: who's selling the item
    :param gid: which item
    :return: null
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    # print('GID: ', gid, type(gid))
    cursor.execute('call create_deal(%s,%s,%s,@suc)' % (buyer_id, seller_id, gid))
    db.commit()
    cursor.execute('select @suc')
    ret = cursor.fetchone()
    # print(ret, type(ret))

    cursor.close()
    db.close()

    return int(ret[0])


def get_user_info(userid):
    """
    given current user ID, return all relative deals and information
    TODO: user's favorites
    :param userid: user's ID
    :return: 3 dictionaries, information, deals and favorites respectively
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    # 获取用户基本信息封装成字典
    cursor.execute('select * from user where uID="%s"' % userid)
    res_info = cursor.fetchone()
    info = {
        'uid': res_info[0],
        'name': res_info[1],
        'sid': res_info[3],
        'campus': res_info[4],
        'tel': res_info[5],
        'vx': res_info[6],
        'qq': res_info[7],
        'email': res_info[8],
        'aut': res_info[9]
    }
    # 获取交易信息并封装成字典数组
    cursor.execute('call get_user_deal(%s)' % userid)
    res_deal = cursor.fetchall()
    deals = []
    for d in res_deal:
        deal_item = {
            'gid': d[0],
            'seller_id': d[1],
            'buyer_id': d[2],
            'time': d[3],
            'price': d[4],
            'status': d[5],
            'goods_name': d[6],
            'seller_name': d[7],
            'buyer_name': d[8]
        }
        deals.append(deal_item)
    # 获取用户收藏信息
    cursor.execute('call get_user_fav(%s)' % userid)
    res_fav = cursor.fetchall()
    fav = []
    for f in res_fav:
        fav.append({
            'gid': f[0],
            'gNmae': f[1],
            'fTime': f[2],
            'seller': f[3]
        })
    # 获取用户上传的商品
    cursor.execute('call get_user_upload(%s)' % userid)
    res_up = cursor.fetchall()
    up = []
    for u in res_up:
        up.append({
            'gid': u[0],
            'gName': u[1],
            'gPrice': u[2],
            'time': u[3]
        })
    cursor.close()
    db.close()
    return info, up, deals, fav


def modify_deal(gid, buyer, seller, op, text):
    """
    modify a deal, index by given gid, buyer id and seller id,
    legal op values are: remove, status, price
    :param gid: goods id
    :param buyer: user id
    :param seller: user id
    :param op: as above
    :param text: set the column as what
    :return: null
    """
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    q = 'call modify_deal(%s,%s,%s,"%s","%s")' % (seller, buyer, gid, op, text)
    print(q)
    cursor.execute(q)
    db.commit()

    cursor.close()
    db.close()


def insert_favorites(uid, gid):
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()
    rt = '收藏成功'
    try:
        cursor.execute('call add_fav("%s","%s")' % (uid, gid))
        db.commit()
    except Exception as e:
        print(e)
        rt = '已在收藏夹中，不能重复收藏'
    finally:
        cursor.close()
        db.close()
        return rt


def remove_goods(gid):
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('call remove_goods(%s)' % gid)
    db.commit()
    res = cursor.fetchone()
    print(res, res[0], type(res[0]))
    if res[0] == 1:
        rt = '删除成功'
    else:
        rt = '删除失败，有相关交易'

    cursor.close()
    db.close()
    return rt


def remove_favorites(uid, gid):
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    cursor.execute('delete from favorites where uid=%s and gid=%s' % (uid, gid))
    db.commit()

    cursor.close()
    db.close()
    return '删除成功'


def modify_info(info):
    db = pymysql.connect(host=config.get('MySQL', 'host'), user=config.get('MySQL', 'user'), password=config.get('MySQL', 'password'), database=config.get('MySQL', 'database'), charset=config.get('MySQL', 'charset'))
    cursor = db.cursor()

    rt = '修改成功'
    try:
        cursor.execute('start transaction')
        cursor.execute('update user set uName="%s" where uID=%s' % (info['uName'], info['uID']))
        for item in info:
            if item != 'uID' and item != 'uName':
                cursor.execute('update user set %s="%s" where uID=%s' % (item, info[item], info['uID']))
        db.commit()
    except Exception as e:
        print(e)
        db.rollback()
        rt = '用户名已存在'
    finally:
        cursor.close()
        db.close()
        return rt


if __name__ == '__main__':
    test = {
        'a': '1',
        'b': '2'
    }
    for i in test:
        print(i, test[i])
